package com.bw.mapper;

import com.bw.bean.Vo;
import com.bw.bean.Vo1;
import com.bw.bean.Vo2;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {

    @Select("select s.name,\n" +
            "sum(o.original_amount) amount1,\n" +
            "count(o.id) num1,\n" +
            "count(distinct  o.customer_id) num2,\n" +
            "sum(o.actual_amount) amount2,\n" +
            "sum(p.amount) amount3\n" +
            "from order_info o\n" +
            " left join  shop s on o.shop_id = s.id\n" +
            "left join  payment p on s.id = p.shop_id\n" +
            "where p.status=5\n" +
            "group by s.name")
    List<Vo1> getList();

    @Select("select date(now()) day, 1 time,\n" +
            "sum(if(type=1,p.reduce_amount,0)) amount1,\n" +
            "sum(if(type=2,p.reduce_amount,0)) amount2,\n" +
            "sum(p.reduce_amount) amount3\n" +
            "from order_info o\n" +
            "left join shop s on o.shop_id = s.id\n" +
            "left join promotion p on o.promotion_id = p.id\n" +
            "where timestampdiff(year,o.create_time,now())<=1\n" +
            "union\n" +
            "select date(now()) day, 7 time,\n" +
            "sum(if(type=1,p.reduce_amount,0)),\n" +
            "sum(if(type=2,p.reduce_amount,0)),\n" +
            "sum(p.reduce_amount)\n" +
            "from order_info o\n" +
            "left join shop s on o.shop_id = s.id\n" +
            "left join promotion p on o.promotion_id = p.id\n" +
            "where timestampdiff(year,o.create_time,now())<=7;\n")
    List<Vo2> getList2();

    @Select("select p.name,\n" +
            "sum(if(o.rating=5,1,0))/count(o.rating) value\n" +
            "from order_info o\n" +
            "left join order_detail od on o.id = od.order_info_id\n" +
            "left join product_sku ps on od.product_sku_id = ps.id\n" +
            "left join product_spu p on ps.product_spu_id = p.id\n" +
            "where p.name is not null\n" +
            "group by p.name;")
    List<Vo> getList3();

    @Select("select p.name,\n" +
            "avg(o.rating) value\n" +
            "from order_info o\n" +
            "left join order_detail od on o.id = od.order_info_id\n" +
            "left join product_sku ps on od.product_sku_id = ps.id\n" +
            "left join product_spu p on ps.product_spu_id = p.id\n" +
            "where p.name is not null\n" +
            "group by p.name;\n")
    List<Vo> getList4();

    @Select("select hour(create_time)name,sum(actual_amount)value from order_info\n" +
            "group by hour(create_time)\n" +
            "order by hour(create_time)")
    List<Vo> getList5();
}
